package com.andnnl.jdframe;


import io.github.burukeyou.dataframe.iframe.JDFrame;
import io.github.burukeyou.dataframe.iframe.SDFrame;
import io.github.burukeyou.dataframe.iframe.item.FI2;
import io.github.burukeyou.dataframe.iframe.item.FI3;
import io.github.burukeyou.dataframe.iframe.item.FI4;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Stream;

/**
 * Created by chenss on 2024/4/28.
 */
public class SumTest {
    static List<Student> studentList = new ArrayList<>();

    static {
        studentList.add(new Student(1, "a", "一中", "一年级", 11, new BigDecimal(1)));
        studentList.add(new Student(2, "a", "一中", "一年级", 11, new BigDecimal(1)));
        studentList.add(new Student(3, "b", "一中", "三年级", 12, new BigDecimal(2)));
        studentList.add(new Student(4, "c", "二中", "一年级", 13, new BigDecimal(3)));
        studentList.add(new Student(5, "d", "二中", "一年级", 14, new BigDecimal(4)));
        studentList.add(new Student(6, "e", "三中", "二年级", 14, new BigDecimal(5)));
        studentList.add(new Student(7, "e", "三中", "二年级", 15, new BigDecimal(5)));
    }

    public static void main(String[] args) {
        testSum();

        testFilter();


        testDistinct();

        testGroup();
    }

    private static void testGroup() {
        JDFrame<Student> frame = JDFrame.read(studentList);
// 等价于 select school,sum(age) ... group by school
        List<FI2<String, BigDecimal>> a = frame.groupBySum(Student::getSchool, Student::getAge).toLists();
// 等价于 select school,max(age) ... group by school
        List<FI2<String, Integer>> a2 = frame.groupByMaxValue(Student::getSchool, Student::getAge).toLists();
        System.out.println(a2);
//  与 groupByMaxValue 含义一致，只是返回的是最大的值对象
        List<FI2<String, Student>> a3 = frame.groupByMax(Student::getSchool, Student::getAge).toLists();
// 等价于 select school,min(age) ... group by school
        List<FI2<String, Integer>> a4 = frame.groupByMinValue(Student::getSchool, Student::getAge).toLists();
// 等价于 select school,count(*) ... group by school
        List<FI2<String, Long>> a5 = frame.groupByCount(Student::getSchool).toLists();
// 等价于 select school,avg(age) ... group by school
        List<FI2<String, BigDecimal>> a6 = frame.groupByAvg(Student::getSchool, Student::getAge).toLists();

// 等价于 select school,sum(age),count(age) group by school
        List<FI3<String, BigDecimal, Long>> a7 = frame.groupBySumCount(Student::getSchool, Student::getAge).toLists();

// (二级分组)等价于 select school,level,sum(age),count(age) group by school,level
        List<FI3<String, String, BigDecimal>> a8 = frame.groupBySum(Student::getSchool, Student::getLevel, Student::getAge).toLists();

// （三级分组）等价于 select school,level,name,sum(age),count(age) group by school,level,name
        List<FI4<String, String, String, BigDecimal>> a9 = frame.groupBySum(Student::getSchool, Student::getLevel, Student::getName, Student::getAge).toLists();
    }

    private static void testDistinct() {
        List<Student> std = null;
        std = SDFrame.read(studentList).distinct().toLists(); // 根据对象hashCode去重
        System.out.println("-----------------");
        std.stream().forEach(System.out::println);
        std = SDFrame.read(studentList).distinct(Student::getSchool).toLists(); // 根据学校名去重
        System.out.println("-----------------");
        std.stream().forEach(System.out::println);
        std = SDFrame.read(studentList).distinct(e -> e.getSchool() + e.getLevel()).toLists(); // 根据学校名拼接级别去重复
        System.out.println("-----------------");
        std.stream().forEach(System.out::println);
        std = SDFrame.read(studentList).distinct(Student::getSchool).distinct(Student::getLevel).toLists(); // 先根据学校名去除重复再根据级别去除重复
        System.out.println("-----------------");
        std.stream().forEach(System.out::println);
    }

    private static void testFilter() {
        SDFrame<Student> sdf = SDFrame.read(studentList)
                .whereBetween(Student::getAge, 3, 6) // 过滤年龄在[3，6]岁的
                .whereBetweenR(Student::getAge, 3, 6) // 过滤年龄在(3，6]岁的, 不含3岁
                .whereBetweenL(Student::getAge, 3, 6)      // 过滤年龄在[3，6)岁的, 不含6岁
                .whereNotNull(Student::getName) // 过滤名字不为空的数据， 兼容了空字符串''的判断
                .whereGt(Student::getAge, 3)    // 过滤年龄大于3岁
                .whereGe(Student::getAge, 3)   // 过滤年龄大于等于3岁
                .whereLt(Student::getAge, 3)  // 过滤年龄小于3岁的
                .whereIn(Student::getAge, Arrays.asList(3, 7, 8)) // 过滤年龄为3岁 或者7岁 或者 8岁的数据
                .whereNotIn(Student::getAge, Arrays.asList(3, 7, 8)) // 过滤年龄不为为3岁 或者7岁 或者 8岁的数据
                .whereEq(Student::getAge, 3) // 过滤年龄等于3岁的数据
                .whereNotEq(Student::getAge, 3) // 过滤年龄不等于3岁的数据
                .whereLike(Student::getName, "jay") // 模糊查询，等价于 like "%jay%"
                .whereLikeLeft(Student::getName, "jay") // 模糊查询，等价于 like "jay%"
                .whereLikeRight(Student::getName, "jay"); // 模糊查询，等价于 like "%jay"
        sdf.stream().forEach(System.out::println);
    }

    private static void testSum() {
        //统计每个学校的里学生年龄不为空并且年龄在9到16岁间的合计分数，并且获取合计分前2名的学校
        // 等价于SQL:
        // select school,sum(score)
        // from students
        // where age is not null and age >=9 and age <= 16
        // group by school
        // order by sum(score) desc
        // limit 2
        SDFrame<FI2<String, BigDecimal>> sdf2 = SDFrame.read(studentList)
                .whereNotNull(Student::getAge)
                .whereBetween(Student::getAge, 9, 16)
                .groupBySum(Student::getSchool, Student::getScore)
                .sortDesc(FI2::getC2)
                .cutFirst(2);

        sdf2.show();
        sdf2.stream().forEach(System.out::println);
    }
}
